package com.bstek.pms.tools.service;

import com.bstek.pms.tools.model.ColumnInfo;
import com.bstek.pms.tools.model.DBProperties;
import com.bstek.pms.tools.model.TableInfo;
import com.bstek.pms.tools.utils.DbUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.Assert;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description 数据库信息查询服务类
 * @Author hans
 * @CreateDate 2020-7-5
 */
public class DBInfoService {

    /**
     * 查询所有的表结构
     * @return
     */
    public static List<TableInfo> getTables(DBProperties dbProperties) throws Exception {
        List<TableInfo> list=new ArrayList<TableInfo>();
        String sql="select rownum n, a.TABLE_NAME tableName,b.comments from user_tables a ,user_tab_comments b where a.TABLE_NAME=b.table_name";
        Connection conn= DbUtils.getConnection(dbProperties);
        Assert.notNull(conn,"数据库连接创建失败，请检查输入");
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String comments=rs.getString("comments") == null ? rs
                        .getString("tableName") : rs.getString("comments");
                TableInfo t = new TableInfo(rs.getInt("n") + "", rs.getString("tableName"), comments);
                list.add(t);
            }
        }finally {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
        return list;
    }
    /**
     * 查询符合条件的table
     *
     * @author hans
     * @createDate 2020年7月6日
     * @param prefix
     *            表名前缀
     * @return
     * @throws Exception
     */
    public static List<TableInfo> getTablesByPrefix(String prefix,DBProperties dbProperties) throws Exception {
        List<TableInfo> tables = new ArrayList<TableInfo>();
        String sql = "select rownum n, a.TABLE_NAME tableName,b.comments from user_tables a ,user_tab_comments b where a.TABLE_NAME=b.table_name and  A.TABLE_NAME LIKE ? ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection(dbProperties);
            // 查询表信息
            ps = conn.prepareStatement(sql);
            ps.setString(1, prefix.toUpperCase() + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                TableInfo table = new TableInfo();
                table.setTableName(rs.getString("tableName"));
                table.setTableConment(rs.getString("comments") == null ? rs
                        .getString("tableName") : rs.getString("comments"));
                table.setRowIndex(rs.getInt("n")+"");
                table.setPrefix(true);
                tables.add(table);
            }
        } finally {
            DbUtils.closeAll(rs, ps, conn);
        }
        return tables;
    }

    /**
     * 查询列信息
     *
     * @author hans
     * @createDate 2017年6月16日
     *
     * @param tab 表名
     * @param conn 数据库连接
     * @return
     * @throws Exception
     */
    @SuppressWarnings("resource")
    public static TableInfo getFields(TableInfo tab,Connection conn) throws Exception {
        List<ColumnInfo> cols = new ArrayList<ColumnInfo>();
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" SELECT A.COLUMN_NAME,");
        sqlBuf.append(" A.DATA_TYPE,");
        sqlBuf.append(" TO_NUMBER(A.DATA_LENGTH) DATA_LENGTH,");
        sqlBuf.append(" TO_NUMBER(NVL(A.DATA_PRECISION, 0)) DATA_PRECISION,");
        sqlBuf.append(" TO_NUMBER(NVL(A.DATA_SCALE, 0)) DATA_SCALE,");
        sqlBuf.append(" A.NULLABLE,");
        sqlBuf.append(" B.COMMENTS,");
        sqlBuf.append(" C.CONSTRAINT_NAME,");
        sqlBuf.append(" C.CONSTRAINT_TYPE");
        sqlBuf.append(" FROM USER_TAB_COLUMNS A");
        sqlBuf.append(" LEFT JOIN USER_COL_COMMENTS B");
        sqlBuf.append(" ON A.TABLE_NAME = B.TABLE_NAME");
        sqlBuf.append(" AND A.COLUMN_NAME = B.COLUMN_NAME");
        sqlBuf.append(" LEFT JOIN (SELECT E.CONSTRAINT_NAME, E.CONSTRAINT_TYPE, F.COLUMN_NAME");
        sqlBuf.append(" FROM USER_CONSTRAINTS E");
        sqlBuf.append(" LEFT JOIN USER_CONS_COLUMNS F");
        sqlBuf.append(" ON E.CONSTRAINT_NAME = F.CONSTRAINT_NAME");
        sqlBuf.append(" WHERE E.TABLE_NAME = ?");
        sqlBuf.append(" AND CONSTRAINT_TYPE IN ('P', 'U')) C");// 只保留：主键、唯一键
        sqlBuf.append(" ON A. COLUMN_NAME = C.COLUMN_NAME");
        sqlBuf.append(" WHERE A.TABLE_NAME = ?");
        sqlBuf.append(" order by  A.COLUMN_ID");
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ColumnInfo> u = new ArrayList<ColumnInfo>();// 唯一约束
        List<ColumnInfo> p = new ArrayList<ColumnInfo>();// 联合主键
        try {
            ps = conn.prepareStatement(sqlBuf.toString());
            ps.setString(1, tab.getTableName().toUpperCase());
            ps.setString(2, tab.getTableName().toUpperCase());
            rs = ps.executeQuery();
            ColumnInfo col;
            while (rs.next()) {
                col = new ColumnInfo();
                String columnName = rs.getString("COLUMN_NAME");
                col.setName(columnName);// 列名
                int dataLength=rs.getInt("DATA_LENGTH");
                col.setLength(dataLength);// 字符长度
                int precision = rs.getInt("DATA_PRECISION");
                col.setPrecision(precision);// number, 总长度
                int scale=rs.getInt("DATA_SCALE");
                col.setScale(scale);// 小数位
                String dataType = rs.getString("DATA_TYPE");
                col.setDataType(dataType);// 类型
                boolean nullable=rs.getString("NULLABLE").equalsIgnoreCase("Y") ? true : false;
                col.setNullable(nullable);// 是否为空
                col.setLabel(rs.getString("COMMENTS"));// 说明文字
                col.setConstraintsName(rs.getString("CONSTRAINT_NAME") == null ? ""
                        : rs.getString("CONSTRAINT_NAME").toString());// 约束名
                String constraintType = rs.getString("CONSTRAINT_TYPE") == null ? ""
                        : rs.getString("CONSTRAINT_TYPE").toString();
                col.setConstraintsType(constraintType);// 约束类型
                if (StringUtils.isNotBlank(constraintType)) {
                    if ("U".equalsIgnoreCase(constraintType)) {
                        u.add(col);
                    } else if ("P".equalsIgnoreCase(constraintType)) {
                        p.add(col);
                    } else {
                        // 暂不处理
                    }
                }
                cols.add(col);
            }
        } finally {
            DbUtils.closePs(ps);
            DbUtils.closeRs(rs);
        }
        tab.setuPrimaryKey(p);//主键
        tab.setUniqueKey(u);// 设置唯一键
        tab.setCols(cols);
        return tab;
    }

    /**
     * 查询表的约束信息
     *
     * @author hans
     * @createDate 2016年5月30日
     *
     * @param tableName
     * @param type
     *            约束类型：<br>
     *            C check on a table; <br>
     *            P primary key; <br>
     *            R referential AKA foreign key; <br>
     *            U unique key; <br>
     *            V check option on a view; <br>
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> getConstraintsInfo(
            String tableName, String type,DBProperties dbProperties) throws Exception {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = "select a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,b.COLUMN_NAME from user_constraints a left join user_cons_columns b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME where a.TABLE_NAME ='"
                + tableName.toUpperCase()
                + "' and CONSTRAINT_TYPE='"
                + type.toUpperCase() + "'";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn=null;
        try {
            conn=DbUtils.getConnection(dbProperties);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("CONSTRAINT_NAME", rs.getString("CONSTRAINT_NAME"));
                map.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeAll(rs,ps,conn);
        }
        return list;
    }

    /**
     * 查询表信息获取模板所需参数并拼装成map
     * @param selectedTab
     * @param conn
     * @throws Exception
     * @return
     */
    public static Map<String, Object> getColMap(TableInfo selectedTab, Connection conn, Map<String,Object> params, String dbUserName) throws Exception {
        String tableName = selectedTab.getTableName().toUpperCase();
        String sql = "select t.table_name,t.comments,column_id,c.column_name,c.comments columnCname,data_type,case when constraint_name is null then 'N' else 'Y' end idType ,nullable,constraint_name from "
                +" ( select table_name, comments from user_tab_comments where table_name = '"+tableName+"')t ,"
                +" (select table_name,column_name,comments from user_col_comments where table_name = '"+tableName+"')c"
                +" left join (select table_name ,column_name ,column_id,data_type ,nullable from all_tab_cols where table_name = '"+tableName+"' and owner = '"+dbUserName.toUpperCase()+"')tn on c.column_name = tn.column_name"
                +" left join (select cu.constraint_name,column_name,au.table_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type='P' and au.table_name = '"+tableName+"' ) u on tn.column_name = u.column_name"
                +" order by column_id ";

        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Map<String, Object>> listData = new ArrayList<Map<String, Object>>();
        Map<String, Object> mapData = new HashMap<String, Object>();
        List<Map<String, Object>> listId = new ArrayList<Map<String, Object>>();
        int idNum = 0;
        while(rs.next()){
            mapData = new HashMap<String, Object>();
            mapData.put("sysName", params.get("sysName"));
            mapData.put("subSysName", params.get("subSysName"));
            mapData.put("tableName", rs.getString("TABLE_NAME"));
            mapData.put("tableCname", rs.getString("COMMENTS"));
            mapData.put("columnId", rs.getString("COLUMN_ID"));
            mapData.put("columnName", rs.getString("COLUMN_NAME"));
            mapData.put("columnCname", rs.getString("COLUMNCNAME"));
            mapData.put("columnType", rs.getString("DATA_TYPE"));
            mapData.put("nullType", rs.getString("NULLABLE"));
            String idType = rs.getString("IDTYPE");
            if ("Y".equals(idType)) {
                Map<String, Object> mapId = new HashMap<String, Object>();
                mapId.put("idNum", ++idNum);
                mapId.put("constraintName", rs.getString("CONSTRAINT_NAME"));
                mapId.put("columnName", rs.getString("COLUMN_NAME"));
                listId.add(mapId);
            }
            mapData.put("idType", idType);
            listData.add(mapData);
        }
        Map<String, Object> retMap = MapUtils.putAll(new HashMap<String, Object>(),new Object[]{
                "pmsDataFormationChange",listData,"pmsDataFormationChangeId",listId
        });
        return retMap;

    }

    /**
     * 获取选择的表
     * @param properties
     * @param params
     * @return
     * @throws Exception
     */
    public static List<TableInfo> getSelectedTabs(DBProperties properties, Map<String,Object> params) throws Exception {
        List<TableInfo> selectTabs=new ArrayList<>();//选中的表
        List<TableInfo> list=(List<TableInfo>)params.get("tables");
        Connection connection = DbUtils.getConnection(properties);
        try{
            for (TableInfo tab : list) {
                if(tab.getCb().isSelected() || tab.isPrefix()){
                    TableInfo newTable= DBInfoService.getFields(tab,connection);
                    selectTabs.add(newTable);
                }
            }
        }finally {
            DbUtils.closeConn(connection);
        }

        return selectTabs;
    }
}
